package com.campus.campusdelivery.repository;

import com.campus.campusdelivery.entity.Order;
import com.campus.campusdelivery.constant.OrderStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    Optional<Order> findByOrderNumber(String orderNumber);
    
    List<Order> findByUserId(Long userId);
    
    List<Order> findByMerchantId(Long merchantId);
    
    List<Order> findByDeliveryPersonId(Long deliveryPersonId);
    
    List<Order> findByStatus(OrderStatus status);
    
    List<Order> findByUserIdAndStatus(Long userId, OrderStatus status);
    
    List<Order> findByMerchantIdAndStatus(Long merchantId, OrderStatus status);
    
    @Query("SELECT o FROM Order o JOIN FETCH o.user JOIN FETCH o.merchant WHERE o.user.id = :userId ORDER BY o.createTime DESC")
    Page<Order> findByUserIdWithPagination(@Param("userId") Long userId, Pageable pageable);
    
    @Query("SELECT o FROM Order o JOIN FETCH o.user JOIN FETCH o.merchant WHERE o.user.id = :userId AND o.status = :status ORDER BY o.createTime DESC")
    Page<Order> findByUserIdAndStatusWithPagination(@Param("userId") Long userId, @Param("status") OrderStatus status, Pageable pageable);
    
    @Query("SELECT o FROM Order o WHERE o.merchant.id = :merchantId ORDER BY o.createTime DESC")
    Page<Order> findByMerchantIdWithPagination(@Param("merchantId") Long merchantId, Pageable pageable);
    
    @Query("SELECT o FROM Order o WHERE o.deliveryPerson.id = :deliveryPersonId ORDER BY o.createTime DESC")
    Page<Order> findByDeliveryPersonIdWithPagination(@Param("deliveryPersonId") Long deliveryPersonId, Pageable pageable);
    
    @Query("SELECT o FROM Order o WHERE o.createTime BETWEEN :startTime AND :endTime")
    List<Order> findByCreateTimeBetween(@Param("startTime") LocalDateTime startTime, 
                                      @Param("endTime") LocalDateTime endTime);
    
    @Query("SELECT o FROM Order o WHERE o.merchant.id = :merchantId AND o.createTime BETWEEN :startTime AND :endTime")
    List<Order> findByMerchantAndTimeRange(@Param("merchantId") Long merchantId,
                                         @Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime);
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.status = :status")
    long countByStatus(@Param("status") OrderStatus status);
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.merchant.id = :merchantId AND o.status = :status")
    long countByMerchantAndStatus(@Param("merchantId") Long merchantId, @Param("status") OrderStatus status);
    
    // 新增方法：支持AdminServiceImpl的需求
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.createTime BETWEEN :startTime AND :endTime")
    long countByCreateTimeBetween(@Param("startTime") LocalDateTime startTime, 
                                @Param("endTime") LocalDateTime endTime);
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.status = :status AND o.createTime BETWEEN :startTime AND :endTime")
    long countByStatusAndCreateTimeBetween(@Param("status") OrderStatus status,
                                         @Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime);
    
    @Query("SELECT COALESCE(SUM(o.finalAmount), 0) FROM Order o WHERE o.status = :status")
    BigDecimal getTotalRevenueByStatus(@Param("status") OrderStatus status);
    
    @Query("SELECT COALESCE(SUM(o.finalAmount), 0) FROM Order o WHERE o.status = :status AND o.createTime BETWEEN :startTime AND :endTime")
    BigDecimal getTotalRevenueByStatusAndCreateTimeBetween(@Param("status") OrderStatus status,
                                                         @Param("startTime") LocalDateTime startTime,
                                                         @Param("endTime") LocalDateTime endTime);
    
    @Query("SELECT o FROM Order o ORDER BY o.createTime DESC LIMIT :limit")
    List<Order> findRecentOrders(@Param("limit") int limit);
}
